# -*- coding:utf-8 -*-
import threading
import time
import os

# coding:utf-8
import time
import os
from datetime import datetime
from threading import Lock, Event

import paramiko
import pymysql
import redis

# r = redis.Redis(host="10.33.64.99", port=6379, db=0)
# conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz", charset="utf8")
r = redis.Redis(host="127.0.0.1", port=6379, db=0)
conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz_test", charset="utf8")

cursor = conn.cursor()


class SysSSHClient(object):
    def __init__(self, hostname, username, password, port=22, keepalive=None):
        self.hostname = hostname
        self.username = username
        self.password = password
        self.port = port
        self.keepalive = keepalive
        self.connected = Event()
        self.lock = Lock()
        self.ssh = paramiko.SSHClient()
        self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

    def _do_connect(self):
        self.ssh.connect(hostname=self.hostname, username=self.username, password=self.password, port=self.port)

    def _connect(self):
        """ Connect to the remote if not already connected. """
        if not self.connected.is_set():
            try:
                self.lock.acquire()
                # Another thread may have connected while we were
                # waiting to acquire the lock
                if not self.connected.is_set():
                    self._do_connect()
                    self.connected.set()
            except :
                raise
            finally:
                self.lock.release()

    def run_command(self, command):
        self._connect()
        stdin, stdout, stderr = self.ssh.exec_command(command)
        return stdout

    def close(self):
        self.ssh.close()

ssh = SysSSHClient(hostname='10.33.64.99', username='root', password='Vdin1234', port=22)
time_list = [ str(i)+':00:00' if i>=10 else '0'+str(i)+':00:00' for i in range(24)]
oids = ['ifOutOctets.777', 'ifInOctets.777', 'ifOutOctets.1025', 'ifInOctets.1025']


def redis_handler(time_str):
    print "Redis_hanler Start....."
    while True:
        t1 = time.time()
        # print "Redis_handler:", t1
        if not time_str == time.strftime("%m%d"):
            r.expire("fw_tx_" + time_str, 24 * 60 * 60)
            r.expire("fw_rx_" + time_str, 24 * 60 * 60)
            r.expire("sw_tx_" + time_str, 24 * 60 * 60)
            r.expire("sw_rx_" + time_str, 24 * 60 * 60)
            time_str = time.strftime("%Y%m%d")
        results = []
        for oid in oids:
            cmd = 'snmpwalk -v 2c -c Vdin1234 1.1.1.1 %s' % oid
            result = ssh.run_command(cmd).read()
            # print result, time.time()
            results.append(result.strip().split()[-1])
        r.rpush("fw_tx_" + time_str, results[0])
        r.rpush("fw_rx_" + time_str, results[1])
        r.rpush("sw_tx_" + time_str, results[2])
        r.rpush("sw_rx_" + time_str, results[3])
        # print results
        time.sleep(0.5)


def sql_handler2(time_str, delta_t):
    print "sql_hander2 ==========================="
    while True:
        t = time.time()
        ft0 = r.rpop("fw_tx_" + time_str)
        fr0 = r.rpop("fw_rx_" + time_str)
        sr0 = r.rpop("sw_rx_" + time_str)
        st0 = r.rpop("sw_tx_" + time_str)
        print "ftrsrt", ft0, fr0, sr0, st0
        time.sleep(delta_t)
        d_ft = int(r.rpop("fw_tx_" + time_str)) - int(ft0)
        d_fr = int(r.rpop("fw_rx_" + time_str)) - int(fr0)
        d_sr = int(r.rpop("sw_rx_" + time_str)) - int(sr0)
        d_st = int(r.rpop("sw_tx_" + time_str)) - int(st0)
        print d_ft, d_fr, d_st, d_sr
        sql = """INSERT INTO app_timeslotnetflow(firewall_tx, firewall_rx, switch_tx, switch_rx, 
                          start_time, end_time) VALUES (%s, %s, %s, %s, %s, %s)"""
        try:
            cursor.execute(sql, (str(d_ft), str(d_fr), str(d_st), str(d_sr),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t - delta_t)),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))))
            conn.commit()
        except Exception as e:
            print e

def sql_handler(time_str, delta_t):
        while True:
            t = time.time() - 5
            print "sql_handler", t
            print "fw_tx:", r.hget("fw_tx_" + time_str, int(t)), r.hget("fw_tx_" + time_str, int(t) - delta_t),
                # r.hget("fw_tx_" + time_str, int(t) - delta_t - 1), r.hget("fw_tx_" + time_str, int(t) - delta_t)
            print "fw_rx:", r.hget("fw_rx_" + time_str, int(t)), r.hget("fw_rx_" + time_str, int(t) - delta_t)

            delta_fw_tx = int(r.hget("fw_tx_" + time_str, int(t))) - int(r.hget("fw_tx_" + time_str, int(t) - delta_t))
            delta_fw_rx = int(r.hget("fw_rx_" + time_str, int(t))) - int(r.hget("fw_rx_" + time_str, int(t) - delta_t))
            delta_sw_tx = int(r.hget("sw_tx_" + time_str, int(t))) - int(r.hget("sw_tx_" + time_str, int(t) - delta_t))
            delta_sw_rx = int(r.hget("sw_rx_" + time_str, int(t))) - int(r.hget("sw_rx_" + time_str, int(t) - delta_t))
            print delta_fw_tx, delta_fw_rx, delta_sw_tx, delta_sw_rx

            sql = """INSERT INTO app_timeslotnetflow(firewall_tx, firewall_rx, switch_tx, switch_rx, 
                  start_time, end_time) VALUES (%s, %s, %s, %s, %s, %s)"""
            try:
                cursor.execute(sql, (str(delta_fw_tx), str(delta_fw_rx), str(delta_sw_tx), str(delta_sw_rx),
                                     time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t - delta_t)),
                                     time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))))
                conn.commit()
            except Exception as e:
                print e
            time.sleep(delta_t)


if __name__ == "__main__":
    event = threading.Event()
    time_str = time.strftime("%m%d")
    t1 = threading.Thread(target=redis_handler, args=(time_str,))
    t1.setDaemon(True)
    t1.start()
    time.sleep(10)
    while True:
        if int(time.strftime("%M")) % 1 == 0:
            # t2 = threading.Thread(target=sql_handler2, args=(time_str, 10))
            # t2.start()
            sql_handler2(time_str, 10)
            break
    # while True:
    #     print "Start Time", time.time()
    #     print ssh.run_command('snmpwalk -v 2c -c Vdin1234 1.1.1.1 ifInOctets.777').read()
    #     print "End Time", time.time()
    #     print "============"
    #     time.sleep(1)